﻿// ==============================================================================
// Copyright (c) 2012 Bndy.Net, All rights reserved.
// Created by Bndy on 3/30/2012 7:01:36 PM
//
//                    * * * * * * * * * * * * * 
//                    * MAIL- bndy5337@163.com *       http://www.bndy.net
//                    * Q Q - 81795705 =========================================>
//                    * MSN - bndy5337@msn.com *
//                    * * * * * * * * * * * * * 
//
// ------------------------------------------------------------------------------
// 
// ==============================================================================

using System.Data;
using System.Data.SqlClient;

namespace Net.Bndy.Data
{
    public class MsSqlFactory : IDbFactory
    {
        public string ConnectionString { get; set; }

        public MsSqlFactory(string connectionString)
        {
            this.ConnectionString = connectionString;
            _dbConnection = new SqlConnection(this.ConnectionString);
            _cmd = new SqlCommand();

            _dbConnection.Open();
            _cmd.Connection = _dbConnection;
        }

        public MsSqlFactory(SqlConnection dbConnection)
        {
            this.ConnectionString = dbConnection.ConnectionString;
            _dbConnection = dbConnection;
        }

        public MsSqlFactory(string server, string databaseName, string user, string password)
            : this(new SqlConnectionStringBuilder(){
                DataSource = server,
                InitialCatalog = databaseName,
                UserID = user,
                Password = password
            }.ConnectionString)
        {
        }

        public int ExecuteCommand(string cmdText)
        {
            _cmd.CommandText = cmdText;

            return _cmd.ExecuteNonQuery();
        }

        public bool ExecuteTransaction(params string[] cmdText)
        {
            bool result = false;

            using (SqlTransaction tran = _dbConnection.BeginTransaction())
            {
                try
                {
                    foreach (string sql in cmdText)
                    {
                        _cmd.CommandText = sql;
                        _cmd.ExecuteNonQuery();
                    }

                    tran.Commit();

                    result = true;
                }
                catch
                {
                    tran.Rollback();
                }
            }

            return result;
        }

        public bool ExecuteTransaction(params SqlCommand[] cmdSet)
        {
            bool result = false;

            using (SqlTransaction tran = _dbConnection.BeginTransaction())
            {
                try
                {
                    foreach (SqlCommand cmd in cmdSet)
                    {
                        cmd.Connection = _dbConnection;
                        cmd.ExecuteNonQuery();
                    }

                    tran.Commit();

                    result = true;
                }
                catch
                {
                    tran.Rollback();
                }
            }

            return result;
        }

        public int ExecuteCount(string tableName, string condition)
        {
            int result = 0;

            _cmd.CommandText = string.Format("SELECT COUNT(1) FROM {0} WHERE {1}", tableName, condition);
            int.TryParse(_cmd.ExecuteScalar().ToString(), out result);

            return result;
        }

        public System.Data.DataTable ExecuteDataTable(string queryString)
        {
            DataTable result = new DataTable();

            _cmd.CommandText = queryString;
            SqlDataAdapter adapter = new SqlDataAdapter(_cmd);
            adapter.Fill(result);

            return result;
        }

        public System.Data.IDataReader ExecuteReader(string queryString)
        {
            _cmd.CommandText = queryString;

            return _cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public object ExecuteScalar(string queryString)
        {
            _cmd.CommandText = queryString;

            return _cmd.ExecuteScalar();
        }

        public void Dispose()
        {
            Dispose(true);

        }

        ~MsSqlFactory()
        {
            Dispose();
        }

        #region Private Members

        private SqlConnection _dbConnection;
        private SqlCommand _cmd;
        private bool _disposed = false;

        private void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    // TODO: Release all managed resources.
                }

                if (_dbConnection.State != System.Data.ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }

                _disposed = true;
            }
        }

        #endregion Private Members
    }
}
